#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "1 days" +%F`
fi

TMP_EVENT_LOG_CLEANED_SQL="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_cleaned;
CREATE TABLE IF NOT EXISTS jtp_app_tmp.tmp_event_log_cleaned
AS
SELECT account,appid,appversion,carrier,deviceid,devicetype,eventid,ip,latitude
     ,longitude,nettype,osname,osversion,properties,releasechannel,
      resolution,sessionid,\`timestamp\`,dt
FROM jtp_app_ods.ods_event_app_log
WHERE dt = '${data_date}'
    AND deviceid IS NOT NULL AND length(deviceid) != 0
    AND eventid IS NOT NULL AND length(eventid) != 0
    AND sessionid IS NOT NULL AND length(sessionid) != 0
    AND properties IS NOT NULL AND size(properties) != 0
;"

TMP_EVENT_LOG_SESSION_SQL="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_session;
CREATE TABLE IF NOT EXISTS jtp_app_tmp.tmp_event_log_session
 AS
 WITH tmp1 AS (
     SELECT
         *
          , \`timestamp\` -LAG(\`timestamp\`, 1, 0) OVER (PARTITION BY deviceid ORDER BY \`timestamp\`) AS diff_ts
     FROM jtp_app_tmp.tmp_event_log_cleaned
 ) ,tmp2 AS (
     SELECT *,
            if(diff_ts /1000 /60 >= 10 ,concat(sessionid,'_',\`timestamp\` ),NULL) AS start_session_id
     FROM tmp1
 )SELECT account, appid, appversion, carrier, deviceid, devicetype, eventid, ip, latitude,
         longitude, nettype, osname, osversion, properties, releasechannel, resolution,
         sessionid, \`timestamp\`, dt,last(start_session_id,true)
         OVER (PARTITION BY sessionid ORDER BY \`timestamp\`) AS new_session_id
      FROM tmp2
 ;"

TMP_EVENT_LOG_AREA_SQL="
DROP TABLE IF EXISTS jtp_app_tmp.tmp_event_log_area;
CREATE TABLE jtp_app_tmp.tmp_event_log_area
 AS
     SELECT t1.*
     ,t2.province,t2.city,t2.district,t2.street
         FROM jtp_app_tmp.tmp_event_log_session t1
  LEFT JOIN jtp_app_dim.dim_md_areas t2
 ON convert_geohash(t1.latitude,t1.longitude,6) = t2.geo_hash;"

DWD_EVENT_LOG_DETAIL_SQL="
INSERT OVERWRITE TABLE jtp_app_dwd.dwd_event_log_detail PARTITION (dt = '${data_date}')
 SELECT account,appid,appversion,carrier,deviceid,devicetype,eventid,ip,latitude,
        longitude,province,city,district,nettype   AS net_type,osname    AS os_name,
        osversion AS os_version,properties,releasechannel,resolution,new_session_id,\`timestamp\`
 FROM jtp_app_tmp.tmp_event_log_area
   ;"


 /opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${TMP_EVENT_LOG_CLEANED_SQL}${TMP_EVENT_LOG_SESSION_SQL}${TMP_EVENT_LOG_AREA_SQL}${DWD_EVENT_LOG_DETAIL_SQL}"